import io
import os
import tempfile
import zipfile
from copy import copy
from importlib.util import source_hash
from pprint import pprint

import xlrd
import xlwt
from openpyxl.drawing.image import Image
from openpyxl.drawing.spreadsheet_drawing import AnchorMarker, OneCellAnchor, TwoCellAnchor
from openpyxl.utils import get_column_letter

from config import input_file



def load_by_header(file,start_row, *headers):
    result = []
    _num, _name, _clazz = '', '', ''
    all_header = get_data_by_row(file, 0, 1, 0, 0)
    header_pos = []
    for title in all_header:
        for i in range(len(title)):
            item = title[i]
            for h in headers:
                if item == h:
                    header_pos.append(i)

    data = get_data_by_row(file, start_row, 0, 0, 0)
    for entry in data:
        row = []
        for h in header_pos:
            row.append(entry[h])
        result.append(row)
    print(result)
    return result



def load(filename):
    data = xlrd.open_workbook(filename)
    table = data.sheets()[0]
    return table

def get_data_col(filename, ncol):
    table = load(filename)
    nrows = table.nrows
    col_list = table.col_values(ncol, start_rowx=0, end_rowx=None)
    # col_list = [str(table.cell_value(i, ncol)) for i in range(1, nrows) if str(table.cell_value(i, ncol)) != '']
    print("第%d列所有的值：%s" % (ncol+1, col_list))
    return col_list

def get_data_row(filename, nrow):
    table = load(filename)
    ncols = table.ncols
    row_list = [str(table.cell_value(nrow,i)) for i in range(1, ncols) if str(table.cell_value(i, nrow)) != '']
    print("第%d行所有的值：%s" % (nrow+1, row_list))
    return row_list

def get_data_cell(filename, row, col):
    table = load(filename)
    data = table.cell_value(row, col)
    print(data)
    return data

# return a list with value of row
def get_data_by_row(filename, start_rowx, end_rowx, start_colx, end_colx):
    result = []
    table = load(filename)
    if end_rowx == 0:
        end_rowx = table.nrows
    if end_colx == 0:
        end_colx = table.ncols
    for rowx in range(start_rowx, end_rowx):
        row = [str(table.cell_value(rowx, i)) for i in range(start_colx, end_colx) if str(table.cell_value(rowx, i)) != '']
        if len(row) > 0:
            # print(row)
            result.append(row)
    return result

# return a list with value of col
def get_data_by_col(filename, start_rowx, end_rowx, start_colx, end_colx):
    result = []
    table = load(filename)
    for colx in range(start_colx, end_colx+1):
        col = [str(table.cell_value(i, colx)) for i in range(start_rowx, end_rowx) if str(table.cell_value(i, colx)) != '']
        if len(col) > 0:
            # print(row)
            result.append(col)
    return result

def get_term(filename):
    table = load(filename)
    return table.cell_value(0, 0)

def write_to_excel(file,header,data):
    workbook = xlwt.Workbook(encoding='utf-8')
    sheet = workbook.add_sheet("sheet1")
    font = xlwt.Font()
    font.name = '宋体'
    font.height = 224   # 12号字
    # font.colour_index = 55
    alignment = xlwt.Alignment()
    #alignment.horz = xlwt.Alignment.HORZ_CENTER  # 水平居中
    alignment.vert = xlwt.Alignment.VERT_CENTER  # 垂直居中
    pattern1 = xlwt.Pattern()
    pattern1.pattern = xlwt.Pattern.SOLID_PATTERN
    # https://blog.csdn.net/BearStarX/article/details/81584434
    pattern1.pattern_fore_colour = 45
    pattern2 = xlwt.Pattern()
    pattern2.pattern = xlwt.Pattern.SOLID_PATTERN
    pattern2.pattern_fore_colour = 43
    borders = xlwt.Borders()
    borders.left = xlwt.Borders.THIN
    borders.right = xlwt.Borders.THIN
    borders.top = xlwt.Borders.THIN
    borders.bottom = xlwt.Borders.THIN
    style = xlwt.XFStyle()
    style.font = font
    style.alignment = alignment
    style.borders = borders

    header_font = xlwt.Font()
    header_font.name = '宋体'
    header_font.height = 300
    header_alignment = xlwt.Alignment()
    header_alignment.horz = xlwt.Alignment.HORZ_CENTER  # 水平居中
    header_alignment.vert = xlwt.Alignment.VERT_CENTER  # 垂直居中
    # header_font.colour_index = 55   # 灰色
    header_style = xlwt.XFStyle()
    header_style.font = header_font
    header_style.alignment = header_alignment
    header_style.borders = borders
    for index, cell in enumerate(header):
        width_col = sheet.col(index)
        width_col = 256 * 20
        sheet.write(0, index, cell, header_style)
    for i in range(len(data)):
        week_number = data[i][0]
        if week_number % 2 ==0:
            style.pattern = pattern1
        else:
            style.pattern = pattern2
        for j in range(len(data[i])):
            entry = str(data[i][j])
            if j == 0:
                entry = f'第{entry}周'
            sheet.write(i + 1, j, entry,style)

    workbook.save(file)

def zip_folder(folder_path, zip_path):
    with zipfile.ZipFile(zip_path, 'w') as zipf:
        for root, _, files in os.walk(folder_path):
            for file in files:
                file_path = os.path.join(root, file)
                zipf.write(file_path, os.path.relpath(file_path, folder_path))

def copy_sheet_with_images(workbook, source_sheet_name, target_sheet_name):
    # 获取源工作表
    source_sheet = workbook[source_sheet_name]

    # 创建目标工作表
    target_sheet = workbook.copy_worksheet(source_sheet)
    target_sheet.title = target_sheet_name

    # 获取源工作表中的所有图片
    images = []
    for drawing in source_sheet._images:
        try:
            if isinstance(drawing.anchor, OneCellAnchor):
                col = drawing.anchor._from.col
                row = drawing.anchor._from.row
                colOff = drawing.anchor._from.colOff
                rowOff = drawing.anchor._from.rowOff
                width = drawing.width
                height = drawing.height
                anchor_type = 'OneCellAnchor'
            elif isinstance(drawing.anchor, TwoCellAnchor):
                col1 = drawing.anchor._from.col
                row1 = drawing.anchor._from.row
                colOff1 = drawing.anchor._from.colOff
                rowOff1 = drawing.anchor._from.rowOff
                col2 = drawing.anchor.to.col
                row2 = drawing.anchor.to.row
                colOff2 = drawing.anchor.to.colOff
                rowOff2 = drawing.anchor.to.rowOff
                width = None
                height = None
                anchor_type = 'TwoCellAnchor'

            # 如果 drawing.ref 是 BytesIO 对象，保存为临时文件
            if hasattr(drawing.ref, 'getvalue'):
                with tempfile.NamedTemporaryFile(delete=False, suffix='.png') as temp_file:
                    temp_file.write(drawing.ref.getvalue())
                    img_path = temp_file.name
            else:
                img_path = str(drawing.ref)

    # 将图片添加到目标工作表中

            new_img = Image(img_path)
            if anchor_type == 'OneCellAnchor':
                new_img.width = width
                new_img.height = height
                marker = AnchorMarker(col=col, colOff=colOff, row=row, rowOff=rowOff)
                new_anchor = OneCellAnchor(_from=marker, ext=new_img.anchor.ext if hasattr(new_img.anchor, 'ext') else None)
                new_img.anchor = new_anchor
            elif anchor_type == 'TwoCellAnchor':
                from_marker = AnchorMarker(col=col1, colOff=colOff1, row=row1, rowOff=rowOff1)
                to_marker = AnchorMarker(col=col2, colOff=colOff2, row=row2, rowOff=rowOff2)
                new_anchor = TwoCellAnchor('twoCell', from_marker, to_marker)
                new_img.anchor = new_anchor
            target_sheet.add_image(new_img)

            # 删除临时文件
            # if os.path.exists(img_path) and img_path.startswith(tempfile.gettempdir()):
            #     os.remove(img_path)
        except Exception as e:
            print(f"Error adding image to sheet: {e}")

    return target_sheet


if __name__ == '__main__':
    # get_data_col('/home/luis/Documents/j0015003刘兴.xls',3)
    # get_data_row('/home/luis/Documents/j0015003刘兴.xls',2)
    result = get_data_by_col(input_file,1,9, 2, 6)
    print(result)